// V-backend/src/controllers/analyticsController.js
const db = require('../models');
const { Op } = require('sequelize');

const analyticsController = {
  // 获取销售数据分析
  getSalesAnalytics: async (req, res) => {
    try {
      const anchorId = req.user.anchor_id;
      const userRole = req.user.role;
      const { period = '7d' } = req.query; // 7d, 30d, 90d, 1y
      
      console.log('📊 获取销售分析数据:', { anchorId, role: userRole, period });

      // 计算时间范围
      const dateRange = calculateDateRange(period);
      
      // 销售趋势数据
      let salesTrendQuery = `
        SELECT 
          DATE(o.created_at) as date,
          SUM(oi.subtotal) as daily_sales,
          COUNT(DISTINCT o.order_id) as daily_orders,
          COUNT(oi.order_item_id) as daily_items
        FROM v_order o
        JOIN v_order_item oi ON o.order_id = oi.order_id
        JOIN v_product p ON oi.product_id = p.product_id
        WHERE o.created_at BETWEEN ? AND ?
        ${userRole !== 'super_admin' ? 'AND p.anchor_id = ?' : ''}
        GROUP BY DATE(o.created_at)
        ORDER BY date ASC
      `;

      const salesTrendResult = await db.sequelize.query(salesTrendQuery, {
        replacements: userRole !== 'super_admin' 
          ? [dateRange.start, dateRange.end, anchorId] 
          : [dateRange.start, dateRange.end],
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 商品销售排行
      let productRankingQuery = `
        SELECT 
          p.product_id,
          p.product_name,
          p.category,
          COUNT(oi.order_item_id) as sales_count,
          SUM(oi.quantity) as total_quantity,
          SUM(oi.subtotal) as total_sales
        FROM v_product p
        LEFT JOIN v_order_item oi ON p.product_id = oi.product_id
        LEFT JOIN v_order o ON oi.order_id = o.order_id
        WHERE (o.created_at IS NULL OR o.created_at BETWEEN ? AND ?)
        ${userRole !== 'super_admin' ? 'AND p.anchor_id = ?' : ''}
        GROUP BY p.product_id, p.product_name, p.category
        ORDER BY total_sales DESC
        LIMIT 10
      `;

      const productRankingResult = await db.sequelize.query(productRankingQuery, {
        replacements: userRole !== 'super_admin' 
          ? [dateRange.start, dateRange.end, anchorId] 
          : [dateRange.start, dateRange.end],
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 订单状态分布
      let orderStatusQuery = `
        SELECT 
          o.status,
          COUNT(DISTINCT o.order_id) as order_count,
          SUM(oi.subtotal) as total_amount
        FROM v_order o
        JOIN v_order_item oi ON o.order_id = oi.order_id
        JOIN v_product p ON oi.product_id = p.product_id
        WHERE o.created_at BETWEEN ? AND ?
        ${userRole !== 'super_admin' ? 'AND p.anchor_id = ?' : ''}
        GROUP BY o.status
      `;

      const orderStatusResult = await db.sequelize.query(orderStatusQuery, {
        replacements: userRole !== 'super_admin' 
          ? [dateRange.start, dateRange.end, anchorId] 
          : [dateRange.start, dateRange.end],
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 分类销售数据
      let categorySalesQuery = `
        SELECT 
          p.category,
          COUNT(oi.order_item_id) as sales_count,
          SUM(oi.subtotal) as total_sales
        FROM v_product p
        LEFT JOIN v_order_item oi ON p.product_id = oi.product_id
        LEFT JOIN v_order o ON oi.order_id = o.order_id
        WHERE (o.created_at IS NULL OR o.created_at BETWEEN ? AND ?)
        ${userRole !== 'super_admin' ? 'AND p.anchor_id = ?' : ''}
        GROUP BY p.category
        HAVING p.category IS NOT NULL AND p.category != ''
        ORDER BY total_sales DESC
      `;

      const categorySalesResult = await db.sequelize.query(categorySalesQuery, {
        replacements: userRole !== 'super_admin' 
          ? [dateRange.start, dateRange.end, anchorId] 
          : [dateRange.start, dateRange.end],
        type: db.Sequelize.QueryTypes.SELECT
      });

      const analyticsData = {
        salesTrend: salesTrendResult,
        productRanking: productRankingResult,
        orderStatus: orderStatusResult,
        categorySales: categorySalesResult,
        period: period,
        dateRange: {
          start: dateRange.start,
          end: dateRange.end
        }
      };

      console.log('✅ 销售分析数据获取成功');

      res.json({
        success: true,
        data: analyticsData
      });

    } catch (error) {
      console.error('❌ 获取销售分析数据失败:', error);
      res.status(500).json({
        success: false,
        message: '获取销售分析数据失败: ' + error.message
      });
    }
  },

  // 获取用户行为分析
  getUserAnalytics: async (req, res) => {
    try {
      const anchorId = req.user.anchor_id;
      const userRole = req.user.role;
      const { period = '30d' } = req.query;

      console.log('👥 获取用户行为分析:', { anchorId, role: userRole, period });

      const dateRange = calculateDateRange(period);

      // 用户增长趋势
      let userGrowthQuery = `
        SELECT 
          DATE(created_at) as date,
          COUNT(audience_id) as daily_new_users
        FROM v_audience 
        WHERE created_at BETWEEN ? AND ?
        GROUP BY DATE(created_at)
        ORDER BY date ASC
      `;

      const userGrowthResult = await db.sequelize.query(userGrowthQuery, {
        replacements: [dateRange.start, dateRange.end],
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 活动参与统计
      let activityParticipationQuery = `
        SELECT 
          DATE(r.registration_time) as date,
          COUNT(r.registration_id) as daily_registrations,
          COUNT(DISTINCT r.audience_id) as daily_unique_users
        FROM v_registration r
        JOIN v_activity a ON r.activity_id = a.activity_id
        WHERE r.registration_time BETWEEN ? AND ?
        ${userRole !== 'super_admin' ? 'AND a.anchor_id = ?' : ''}
        GROUP BY DATE(r.registration_time)
        ORDER BY date ASC
      `;

      const activityParticipationResult = await db.sequelize.query(activityParticipationQuery, {
        replacements: userRole !== 'super_admin' 
          ? [dateRange.start, dateRange.end, anchorId] 
          : [dateRange.start, dateRange.end],
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 用户活跃度统计
      let userActivityQuery = `
        SELECT 
          activity_level,
          COUNT(*) as user_count
        FROM (
          SELECT 
            a.audience_id,
            CASE 
              WHEN COUNT(DISTINCT r.registration_id) >= 5 THEN '高活跃'
              WHEN COUNT(DISTINCT r.registration_id) >= 2 THEN '中活跃' 
              ELSE '低活跃'
            END as activity_level
          FROM v_audience a
          LEFT JOIN v_registration r ON a.audience_id = r.audience_id
          LEFT JOIN v_activity act ON r.activity_id = act.activity_id
          WHERE (r.registration_time IS NULL OR r.registration_time BETWEEN ? AND ?)
          ${userRole !== 'super_admin' ? 'AND (act.anchor_id IS NULL OR act.anchor_id = ?)' : ''}
          GROUP BY a.audience_id
        ) as user_activity
        GROUP BY activity_level
      `;

      const userActivityResult = await db.sequelize.query(userActivityQuery, {
        replacements: userRole !== 'super_admin' 
          ? [dateRange.start, dateRange.end, anchorId] 
          : [dateRange.start, dateRange.end],
        type: db.Sequelize.QueryTypes.SELECT
      });

      const userAnalyticsData = {
        userGrowth: userGrowthResult,
        activityParticipation: activityParticipationResult,
        userActivity: userActivityResult,
        period: period,
        dateRange: {
          start: dateRange.start,
          end: dateRange.end
        }
      };

      console.log('✅ 用户行为分析数据获取成功');

      res.json({
        success: true,
        data: userAnalyticsData
      });

    } catch (error) {
      console.error('❌ 获取用户行为分析失败:', error);
      res.status(500).json({
        success: false,
        message: '获取用户行为分析失败: ' + error.message
      });
    }
  },

  // 获取活动效果分析
  getActivityAnalytics: async (req, res) => {
    try {
      const anchorId = req.user.anchor_id;
      const userRole = req.user.role;
      const { period = '90d' } = req.query;

      console.log('🎭 获取活动效果分析:', { anchorId, role: userRole, period });

      const dateRange = calculateDateRange(period);

      // 活动效果统计
      let activityPerformanceQuery = `
        SELECT 
          a.activity_id,
          a.title,
          a.start_time,
          a.status,
          a.max_participants,
          a.current_participants,
          COUNT(r.registration_id) as total_registrations,
          ROUND((a.current_participants / a.max_participants) * 100, 2) as participation_rate
        FROM v_activity a
        LEFT JOIN v_registration r ON a.activity_id = r.activity_id
        WHERE a.created_at BETWEEN ? AND ?
        ${userRole !== 'super_admin' ? 'AND a.anchor_id = ?' : ''}
        GROUP BY a.activity_id, a.title, a.start_time, a.status, a.max_participants, a.current_participants
        ORDER BY total_registrations DESC
      `;

      const activityPerformanceResult = await db.sequelize.query(activityPerformanceQuery, {
        replacements: userRole !== 'super_admin' 
          ? [dateRange.start, dateRange.end, anchorId] 
          : [dateRange.start, dateRange.end],
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 活动状态分布
      let activityStatusQuery = `
        SELECT 
          status,
          COUNT(*) as activity_count,
          SUM(current_participants) as total_participants
        FROM v_activity 
        WHERE created_at BETWEEN ? AND ?
        ${userRole !== 'super_admin' ? 'AND anchor_id = ?' : ''}
        GROUP BY status
      `;

      const activityStatusResult = await db.sequelize.query(activityStatusQuery, {
        replacements: userRole !== 'super_admin' 
          ? [dateRange.start, dateRange.end, anchorId] 
          : [dateRange.start, dateRange.end],
        type: db.Sequelize.QueryTypes.SELECT
      });

      // 热门活动排行
      let popularActivitiesQuery = `
        SELECT 
          a.activity_id,
          a.title,
          a.anchor_id,
          an.anchor_name,
          COUNT(r.registration_id) as registration_count,
          a.current_participants
        FROM v_activity a
        LEFT JOIN v_registration r ON a.activity_id = r.activity_id
        LEFT JOIN v_anchor an ON a.anchor_id = an.anchor_id
        WHERE a.created_at BETWEEN ? AND ?
        ${userRole !== 'super_admin' ? 'AND a.anchor_id = ?' : ''}
        GROUP BY a.activity_id, a.title, a.anchor_id, an.anchor_name, a.current_participants
        ORDER BY registration_count DESC
        LIMIT 10
      `;

      const popularActivitiesResult = await db.sequelize.query(popularActivitiesQuery, {
        replacements: userRole !== 'super_admin' 
          ? [dateRange.start, dateRange.end, anchorId] 
          : [dateRange.start, dateRange.end],
        type: db.Sequelize.QueryTypes.SELECT
      });

      const activityAnalyticsData = {
        activityPerformance: activityPerformanceResult,
        activityStatus: activityStatusResult,
        popularActivities: popularActivitiesResult,
        period: period,
        dateRange: {
          start: dateRange.start,
          end: dateRange.end
        }
      };

      console.log('✅ 活动效果分析数据获取成功');

      res.json({
        success: true,
        data: activityAnalyticsData
      });

    } catch (error) {
      console.error('❌ 获取活动效果分析失败:', error);
      res.status(500).json({
        success: false,
        message: '获取活动效果分析失败: ' + error.message
      });
    }
  },

  // 获取综合数据概览
  getOverviewAnalytics: async (req, res) => {
    try {
      const anchorId = req.user.anchor_id;
      const userRole = req.user.role;

      console.log('📈 获取综合数据概览:', { anchorId, role: userRole });

      // 总用户数
      const totalUsers = await db.Audience.count();

      // 总活动数
      let totalActivitiesWhere = {};
      if (userRole !== 'super_admin') {
        totalActivitiesWhere.anchor_id = anchorId;
      }
      const totalActivities = await db.Activity.count({ where: totalActivitiesWhere });

      // 总商品数
      let totalProductsWhere = {};
      if (userRole !== 'super_admin') {
        totalProductsWhere.anchor_id = anchorId;
      }
      const totalProducts = await db.Product.count({ where: totalProductsWhere });

      // 总销售额
      let totalSalesQuery = `
        SELECT SUM(oi.subtotal) as total_sales
        FROM v_order_item oi
        JOIN v_product p ON oi.product_id = p.product_id
        ${userRole !== 'super_admin' ? 'WHERE p.anchor_id = ?' : ''}
      `;

      const totalSalesResult = await db.sequelize.query(totalSalesQuery, {
        replacements: userRole !== 'super_admin' ? [anchorId] : [],
        type: db.Sequelize.QueryTypes.SELECT
      });
      const totalSales = parseFloat(totalSalesResult[0]?.total_sales || 0);

      // 月增长率计算（简化版）
      const currentMonth = new Date();
      const lastMonth = new Date(currentMonth.getFullYear(), currentMonth.getMonth() - 1, 1);

      // 月度销售额对比
      let monthlySalesQuery = `
        SELECT 
          SUM(oi.subtotal) as monthly_sales
        FROM v_order_item oi
        JOIN v_product p ON oi.product_id = p.product_id
        JOIN v_order o ON oi.order_id = o.order_id
        WHERE o.created_at >= ? AND o.created_at < ?
        ${userRole !== 'super_admin' ? 'AND p.anchor_id = ?' : ''}
      `;

      const currentMonthSalesResult = await db.sequelize.query(monthlySalesQuery, {
        replacements: userRole !== 'super_admin' 
          ? [new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1), currentMonth, anchorId]
          : [new Date(currentMonth.getFullYear(), currentMonth.getMonth(), 1), currentMonth],
        type: db.Sequelize.QueryTypes.SELECT
      });

      const lastMonthSalesResult = await db.sequelize.query(monthlySalesQuery, {
        replacements: userRole !== 'super_admin' 
          ? [new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1), lastMonth, anchorId]
          : [new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1), lastMonth],
        type: db.Sequelize.QueryTypes.SELECT
      });

      const currentMonthSales = parseFloat(currentMonthSalesResult[0]?.monthly_sales || 0);
      const lastMonthSales = parseFloat(lastMonthSalesResult[0]?.monthly_sales || 0);
      
      const salesGrowthRate = lastMonthSales > 0 
        ? ((currentMonthSales - lastMonthSales) / lastMonthSales * 100).toFixed(2)
        : currentMonthSales > 0 ? 100 : 0;

      const overviewData = {
        totalUsers,
        totalActivities,
        totalProducts,
        totalSales: totalSales.toFixed(2),
        monthlySales: currentMonthSales.toFixed(2),
        salesGrowthRate: parseFloat(salesGrowthRate),
        lastUpdated: new Date()
      };

      console.log('✅ 综合数据概览获取成功');

      res.json({
        success: true,
        data: overviewData
      });

    } catch (error) {
      console.error('❌ 获取综合数据概览失败:', error);
      res.status(500).json({
        success: false,
        message: '获取综合数据概览失败: ' + error.message
      });
    }
  }
};

// 辅助函数：计算时间范围
function calculateDateRange(period) {
  const end = new Date();
  let start = new Date();

  switch (period) {
    case '7d':
      start.setDate(end.getDate() - 7);
      break;
    case '30d':
      start.setDate(end.getDate() - 30);
      break;
    case '90d':
      start.setDate(end.getDate() - 90);
      break;
    case '1y':
      start.setFullYear(end.getFullYear() - 1);
      break;
    default:
      start.setDate(end.getDate() - 7);
  }

  start.setHours(0, 0, 0, 0);
  end.setHours(23, 59, 59, 999);

  return { start, end };
}

module.exports = analyticsController;